﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using Maticsoft.DBUtility;
using System.Web;
using MySql.Data.MySqlClient;
using EnvironmentManagerSystem.MODE;

namespace EnvironmentManagerSystem.BLL
{
    public class XIANGMU
    {
        /// <summary>
        /// 项目受理
        /// </summary>
        /// <returns></returns>
        //public static bool ProAcceptance()
        //{
        //    try
        //    {
        //        StringBuilder br = new StringBuilder();
        //        br.AppendFormat("UPDATE tbs001_developprojectbasicinfo t SET ");
        //        br.AppendFormat("t.shoulituihuishanchu=1, ");
        //        br.AppendFormat("t.xiangmushouliren='{0}', ", HttpContext.Current.Session["UserName"].ToString());
        //        br.AppendFormat("t.xiangmushoulishijian='{0}', ", DateTime.Now.ToString("yyyy-MM-dd"));
        //        br.AppendFormat("t.noticestate=0 ");
        //        br.AppendFormat("WHERE t.ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
        //        return ProOper(br.ToString());
        //    }
        //    catch
        //    { return false; }
        //}

        #region 项目受理(x)
        public static bool ProAcceptance()
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_developprojectbasicinfo SET shoulituihuishanchu=1,noticestate=0, xiangmushouliren='{0}',xiangmushoulishijian = '{1}',ShouLiTrace =CONCAT(ShouLiTrace,'|{0}已于{1}进行项目受理信息操作)'),DangQianLiuCheng='项目受理',DangQianLiuChengBu='03' WHERE ProjectName = '{2}'",
                                                        HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        /// <summary>
        /// 项目流程操作
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public static bool ProOper(string Sql)
        {
            List<string> list = new List<string>();
            list.Add(Sql);
            list.Add(NoticeService.EndNotice());
            return DbHelperMySQL.ExecuteSqlTran(list) > 0;
        }
        /// <summary>
        /// 添加复审会议信息
        /// </summary>
        public static bool SetFuShen(DevelopprojectbasicinfoData model)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" shangwu = '{0}',", model.shangwu);
                br.AppendFormat(" xiawu = '{0}',", model.xiawu);
                br.AppendFormat(" pingguyijianhuiyiriqi = '{0}', ", model.pingguyijianhuiyiriqi);
                br.AppendFormat(" pingguyijianhuiyididian = '{0}', ", model.pingguyijianhuiyididian);
                br.AppendFormat(" fushenhuiyipingshenjieguo = '{0}', ", model.fushenhuiyipingshenjieguo);
                br.AppendFormat(" fushenhuiyiquerenren = '{0}', ", HttpContext.Current.Session["UserName"].ToString());
                br.AppendFormat(" fushenhuiyiquerenriqi = '{0}', ", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" pingguyijianxiangmuzhuangtai = '{0}',", model.pingguyijianxiangmuzhuangtai);
                br.AppendFormat(" yijianbeizhu = '{0}',", model.yijianbeizhu);
                br.AppendFormat(" huiyipingshenjieguo = '{0}', ", model.huiyipingshenjieguo);
                br.AppendFormat(" buhegeshuoming = '{0}', ", model.buhegeshuoming);
                br.AppendFormat(" buhegetiaokuan = '{0}', ", model.buhegetiaokuan);
                TraceOper(ref br, "pingguyijianchuliren", "pingguyijianchuliriqi", "OpinionTrace");
                br.Append(" queren  = 1, noticestate=0 ");
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 评估意见信息修改
        /// </summary>
        public static bool SetOpinion(DevelopprojectbasicinfoData model)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" pingguyijianxiangmuzhuangtai = '{0}',", model.pingguyijianxiangmuzhuangtai);
                br.AppendFormat(" yijianbeizhu = '{0}',", model.yijianbeizhu);
                br.AppendFormat(" huiyipingshenjieguo = '{0}',", model.huiyipingshenjieguo);
                br.AppendFormat(" buhegeshuoming = '{0}',", model.buhegeshuoming);
                br.AppendFormat(" buhegetiaokuan = '{0}',", model.buhegetiaokuan);
                br.AppendFormat(" pingguyijianchuliren  = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                br.AppendFormat(" pingguyijianchuliriqi  = '{0}',", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                TraceOper(ref br, "pingguyijianchuliren", "pingguyijianchuliriqi", "OpinionTrace");
                br.Append(" queren  = 1, noticestate=0,DangQianLiuCheng='评估意见',DangQianLiuChengBu='08' ");
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 复审会议返回
        /// </summary>
        public static bool SetCel()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" pingguyijianchuliren  = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                br.AppendFormat(" pingguyijianchuliriqi  = '{0}',", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.Append(" queren  = 0 ");
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0;
            }
            catch
            {
                return false;
            }
        }

        #region 现场踏查

        /// <summary>
        /// 获取专家列表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetZhuanJia()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select Name from tbs001_expert order by id desc ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 设定会议日期
        /// </summary>
        public static bool SetMeetingDate(Hashtable hs)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" zhuanjiahuiyiriqiquerenren = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                br.AppendFormat(" zhuanjiahuiyiriqiquerenriqi = '{0}', ", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" ExpertMeetingTrace = CONCAT(ExpertMeetingTrace,'|会议日期已确认({0}){1}'), ", HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" zhuanjiahuiyichuliriqi = '{0}', ", hs["zhuanjiahuiyichuliriqi"].ToString());
                br.AppendFormat(" zhuanjiahuiyiriqiflag = '{0}' ", hs["zhuanjiahuiyiriqiflag"].ToString());
                br.AppendFormat(" where ProjectName ='{0}'", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0;
            }
            catch { return false; }
        }

        /// <summary>
        /// 设定踏查日期
        /// </summary>
        public static bool SetTCRQ(string Date)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" tachahuiyiriqiquerenren = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                br.AppendFormat(" tachahuiyiriqiquerenriqi = '{0}', ", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" LookTrace = CONCAT(LookTrace,'|踏查日期已确认({0}){1}'), ", HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" tachariqi = '{0}' ", Date);
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0;
            }
            catch { return false; }
        }

        /// <summary>
        /// 操作痕迹处理
        /// </summary>
        /// <param name="br"></param>
        /// <param name="OpenPeople"></param>
        /// <param name="OperDate"></param>
        /// <param name="TraceField"></param>
        public static void TraceOper(ref StringBuilder br, string OpenPeople, string OperDate, string TraceField)
        {
            DataTable dt = GetProjectInfo();
            string TraceInfo = string.Empty;
            if (WhetherLeader(HttpContext.Current.Session["UserName"].ToString()))
            {
                TraceInfo = "已确认";
                if (dt.Rows[0]["" + OpenPeople + ""].ToString() == string.Empty || !WhetherLeader(dt.Rows[0]["" + OpenPeople + ""].ToString()))
                {
                    br.AppendFormat("{0} = '{1}',", OpenPeople, HttpContext.Current.Session["UserName"].ToString());
                    br.AppendFormat("{0} = '{1}',", OperDate, DateTime.Now.Date.ToString("yyyy-MM-dd"));
                }
            }
            else
            {
                TraceInfo = "已处理";
                if (dt.Rows[0]["" + OpenPeople + ""].ToString() == string.Empty)
                {
                    br.AppendFormat("{0} = '{1}',", OpenPeople, HttpContext.Current.Session["UserName"].ToString());
                    br.AppendFormat("{0} = '{1}',", OperDate, DateTime.Now.Date.ToString("yyyy-MM-dd"));
                }
            }
            br.AppendFormat("{0} = CONCAT({1},'|{2}({3}){4}'),", TraceField, TraceField, TraceInfo, HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
        }

        /// <summary>
        /// 项目考评确认
        /// </summary>
        /// <param name="hs"></param>
        /// <returns></returns>
        public static bool AppraisalConfirm(Hashtable hs)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                TraceOper(ref br, "xiangmukaopingchuliren", "xiangmukaopingchuliriqi", "AppraisalTrace");
                br.AppendFormat(" chugaokaoping = '{0}',", hs["chugaokaoping"].ToString());
                br.AppendFormat(" zhonggaokaoping = '{0}'", hs["zhonggaokaoping"].ToString());

                br.AppendFormat(",DangQianLiuCheng='项目考评',DangQianLiuChengBu='09'");
                //br.AppendFormat(" huanpingdanweikaoping = '{0}', noticestate=0", hs["huanpingdanweikaoping"].ToString());
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch { return false; }
        }

        /// <summary>
        /// 专家会议确定
        /// </summary>
        /// <param name="hs"></param>
        /// <returns></returns>
        public static bool MeetingConfirm(Hashtable hs)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                TraceOper(ref br, "zhuanjiahuiyiquerenren", "zhuanjiahuiyiquerenriqi", "ExpertMeetingTrace");
                br.AppendFormat(" huiyiqueren=1,");
                br.AppendFormat(" zhuanjiahuiyididian = '{0}',", hs["zhuanjiahuiyididian"].ToString());
                br.AppendFormat(" zhuanjiahuiyizhuanjian = '{0}', noticestate=0,DangQianLiuCheng='专家会议',DangQianLiuChengBu='07'", hs["zhuanjiahuiyizhuanjian"].ToString());
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 踏查以确定
        /// </summary>
        public static bool SetTCQD(Hashtable hs)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                TraceOper(ref br, "tachaquerenren", "tachaquerenriqi", "LookTrace");
                br.AppendFormat(" tachaquerentuihui=1,");
                br.AppendFormat(" tachadidian = '{0}',", hs["tachadidian"].ToString());
                br.AppendFormat(" tachazhuanjia = '{0}', noticestate=0,DangQianLiuCheng='现场踏查',DangQianLiuChengBu='06'", hs["tachazhuanjia"].ToString());
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch { return false; }
        }

        /// <summary>
        /// 退回
        /// </summary>
        public static bool SetTCTH()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" ResponTrace = CONCAT(ResponTrace,'|已退回({0}){1}'),", HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
                //br.AppendFormat(" tachaquerenren = '{0}',fuzetuihuiqueren=NULL,xiangmufuzequerenren=NULL,", HttpContext.Current.Session["UserName"].ToString());
                //br.AppendFormat(" tachaquerenriqi = '{0}',pingshenxiangmufuzeren=NULL ", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" tachaquerenren = NULL,fuzetuihuiqueren=NULL,xiangmufuzequerenren=NULL,");
                br.AppendFormat(" tachaquerenriqi = NULL,tachaquerentuihui=0,pingshenxiangmufuzeren=NULL, ");


                br.AppendFormat(" tachadidian=NULL,tachazhuanjia=NULL,zhuanjiahuiyiquerenren=NULL, ");
                br.AppendFormat(" zhuanjiahuiyiquerenriqi=NULL,huiyiqueren=NULL,zhuanjiahuiyididian=NULL, ");
                br.AppendFormat(" zhuanjiahuiyizhuanjian=NULL,shangwu=NULL,xiawu=NULL, ");
                br.AppendFormat(" pingguyijianhuiyiriqi=NULL,pingguyijianhuiyididian=NULL,fushenhuiyipingshenjieguo=NULL, ");
                br.AppendFormat(" fushenhuiyiquerenren=NULL,fushenhuiyiquerenriqi=NULL,pingguyijianxiangmuzhuangtai=NULL, ");
                br.AppendFormat(" yijianbeizhu=NULL,huiyipingshenjieguo=NULL,buhegeshuoming=NULL, ");
                br.AppendFormat(" buhegetiaokuan=NULL,pingguyijianchuliren=NULL,pingguyijianchuliriqi=NULL, ");
                br.AppendFormat(" queren=NULL,xiangmukaopingchuliren=NULL,xiangmukaopingchuliriqi=NULL, ");
                br.AppendFormat(" chugaokaoping=NULL,zhonggaokaoping=NULL,huanpingdanweikaoping=NULL, ");
                br.AppendFormat(" reportpeople=NULL,reportdate=NULL,huizongshangbaoshumiancailiao=NULL,reportstate=NULL ");

                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region 项目负责人

        /// <summary>
        /// 项目负责人
        /// </summary>
        /// <returns></returns>
        public static DataTable GetFuzeren(string Department)
        {
            try
            {
                string sql = string.Empty;
                if (Department == string.Empty)
                {
                    sql = "select UserName,ID from tbs001_user where RoleID like '%项目负责人%' or RoleID like '%部门负责人%'";
                    return DbHelperMySQL.Query(sql).Tables[0];
                }
                else
                {
                    sql = string.Format(@"SELECT ed_User FROM tbs001_executivedepartment WHERE ed_Name = '{0}'", Department);
                    using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(sql))
                    {
                        while (dr.Read())
                        {
                            string ed_User = dr["ed_User"].ToString();
                            ed_User = ed_User.Trim(',');
                            sql = string.Format(@"SELECT UserName,ID FROM tbs001_user WHERE ID IN ({0}) GROUP BY UserName ", ed_User);
                        }
                        return DbHelperMySQL.Query(sql).Tables[0];
                    }
                }
            }
            catch { return null; }
        }

        /// <summary>
        /// 负责人确定
        /// </summary>
        public static void SetFuzeren()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" tachaquerenren = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                br.AppendFormat(" tachaquerenriqi = '{0}',tachaquerentuihui=1 ", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                DbHelperMySQL.ExecuteSql(br.ToString());
            }
            catch { }
        }

        /// <summary>
        /// 项目负责确定
        /// </summary>
        public static bool SetXMQD(Hashtable hs)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                TraceOper(ref br, "xiangmufuzequerenren", "xiangmufuzequerenriqi", "ResponTrace");
                br.AppendFormat(" pingshenxiangmufuzeren  = '{0}',tachaquerentuihui=IF(tachaquerentuihui = 0,NULL,tachaquerentuihui),fuzetuihuiqueren=1,DangQianLiuCheng='项目负责',DangQianLiuChengBu='05'", hs["fuzeren"].ToString());
                br.AppendFormat(" , noticestate=0 where ProjectName ='{0}'", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch { return false; }
        }

        /// <summary>
        /// 退回
        /// </summary>
        public static bool SetXMTH()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                //br.AppendFormat(" xiangmufuzequerenren  = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                //br.AppendFormat(" xiangmufuzequerenriqi  = '{0}',fuzetuihuiqueren=0, ", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" xiangmufuzequerenren  = NULL,");
                br.AppendFormat(" xiangmufuzequerenriqi  = NULL,fuzetuihuiqueren=0, ");
                br.AppendFormat(" DistributionTrace = CONCAT(DistributionTrace,'|已退回({0}){1}'),", HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" fenpeizhixingbumen=NULL,pingshenxiangmufuzeren=NULL,fenpeiquerenren=NULL, ");


                br.AppendFormat(" tachaquerenren=NULL,tachaquerenriqi=NULL,tachaquerentuihui=NULL, ");
                br.AppendFormat(" tachadidian=NULL,tachazhuanjia=NULL,zhuanjiahuiyiquerenren=NULL, ");
                br.AppendFormat(" zhuanjiahuiyiquerenriqi=NULL,huiyiqueren=NULL,zhuanjiahuiyididian=NULL, ");
                br.AppendFormat(" zhuanjiahuiyizhuanjian=NULL,shangwu=NULL,xiawu=NULL, ");
                br.AppendFormat(" pingguyijianhuiyiriqi=NULL,pingguyijianhuiyididian=NULL,fushenhuiyipingshenjieguo=NULL, ");
                br.AppendFormat(" fushenhuiyiquerenren=NULL,fushenhuiyiquerenriqi=NULL,pingguyijianxiangmuzhuangtai=NULL, ");
                br.AppendFormat(" yijianbeizhu=NULL,huiyipingshenjieguo=NULL,buhegeshuoming=NULL, ");
                br.AppendFormat(" buhegetiaokuan=NULL,pingguyijianchuliren=NULL,pingguyijianchuliriqi=NULL, ");
                br.AppendFormat(" queren=NULL,xiangmukaopingchuliren=NULL,xiangmukaopingchuliriqi=NULL, ");
                br.AppendFormat(" chugaokaoping=NULL,zhonggaokaoping=NULL,huanpingdanweikaoping=NULL, ");
                br.AppendFormat(" reportpeople=NULL,reportdate=NULL,huizongshangbaoshumiancailiao=NULL,reportstate=NULL ");
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0;
            }
            catch { return false; }
        }

        #endregion

        //部门确定
        public static bool SetBM(Hashtable hs)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                br.AppendFormat(" hantacha = {0},", hs["hantacha"].ToString());
                br.AppendFormat(" hanhuiyi = {0},", hs["hanhuiyi"].ToString());
                br.AppendFormat(" lvsetongdao = {0},", hs["lvsetongdao"].ToString());
                br.AppendFormat(" biangengxiangmu = {0},", hs["biangengxiangmu"].ToString());
                //br.AppendFormat(" yushenxiangmu = {0},", hs["yushenxiangmu"].ToString());
                br.AppendFormat(" fenpeizhixingbumen  = '{0}',", hs["txtbumen"].ToString());

                DataTable dt = GetProjectInfo();
                if (dt.Rows[0]["fenpeiquerenren"].ToString() == string.Empty)
                {
                    br.AppendFormat(" fenpeiquerenren = '{0}',", HttpContext.Current.Session["UserName"].ToString());
                    br.AppendFormat(" fenpeiquerenshijian  = '{0}',", DateTime.Now.Date.ToString("yyyy-MM-dd"));
                    br.AppendFormat(" DistributionTrace = CONCAT(DistributionTrace,'|已分配({0}){1}'),", HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
                }
                else
                    br.AppendFormat(" DistributionTrace = CONCAT(DistributionTrace,'|已分配({0}){1}'),", HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"));
                br.AppendFormat(" fuzetuihuiqueren=NULL,tachaquerentuihui=NULL, noticestate=0,DangQianLiuCheng='部门分配',DangQianLiuChengBu='04'");
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch { return false; }
        }

        /// <summary>
        /// 修改项目备注
        /// </summary>
        /// <param name="Remark"></param>
        /// <returns></returns>
        public static bool UpdateProjectRemark(string Remark)
        {
            try
            {
                string strSql = string.Format(@"UPDATE `tbs001_developprojectbasicinfo` set ProjectMemo='{0}',xiangmubeizhuxiugairen='{1}',xiangmubeizhuxiugaishijian='{2}',MemoTrace =CONCAT(MemoTrace,'|{1}已于{2}进行了项目备注信息修改操作)') WHERE ProjectName = '{3}'",
                                                        Remark, HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 修改项目日志
        /// </summary>
        /// <param name="Log"></param>
        /// <returns></returns>
        public static bool UpdateProjectLog(string Log)
        {
            try
            {
                string strSql = string.Format(@"UPDATE `tbs001_developprojectbasicinfo` set ProjectLog='{0}',xiangmurizhixiugairen='{1}',xiangmurizhi修改shijian='{2}',LogTrace =CONCAT(LogTrace,'|{1}已于{2}进行了项目日志信息修改操作</br>') WHERE ProjectName = '{3}'",
                                                        Log, HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 获取项目信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetProjectInfo()
        {
            try
            {
                string strSql = string.Format(@"SELECT c.Phone as hesuanrenphone,c.UserName,a.*,b.* FROM tbs001_constructionunit b INNER JOIN tbs001_developprojectbasicinfo a ON a.js = b.ID LEFT JOIN tbs001_user c ON a.hesuanren = c.UserName where a.ProjectName='{0}'", HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.Query(strSql).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取未收款项目列表
        /// </summary>
        /// <param name="ProName"></param>
        /// <param name="ConstructionUnit"></param>
        /// <param name="EvaluationUnit"></param>
        /// <param name="money"></param>
        /// <returns></returns>
        public static DataTable GetNonReceive(string ProName, string ConstructionUnit, string EvaluationUnit, string money)
        {
            try
            {
                string strSql = string.Format(@"SELECT 
                                                          a.*,
                                                          b.NAME,
                                                          c.unitname 
                                                        FROM
                                                          tbs001_developprojectbasicinfo a 
                                                          INNER JOIN
                                                          tbs001_constructionunit b 
                                                          ON a.js = b.ID 
                                                          INNER JOIN
                                                          tbs001_evaluationunit c 
                                                          ON a.hp = c.ID 
                                                         WHERE a.ReceiveState !=1 and a.hedinghetongjine is not NULL");
                if (ProName != string.Empty)
                    strSql += string.Format(" and a.ProjectName like '%{0}%'", ProName);
                if (ConstructionUnit != string.Empty)
                    strSql += string.Format(" and b.NAME like '%{0}%'", ConstructionUnit);
                if (EvaluationUnit != string.Empty)
                    strSql += string.Format(" and c.unitname like '%{0}%'", EvaluationUnit);
                if (money != string.Empty)
                    strSql += string.Format(" and a.hedinghetongjine like '%{0}%'", money);
                return DbHelperMySQL.Query(strSql).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取已收款项目列表
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public static DataTable GetAlreadyReceive(string StartDate, string EndDate)
        {
            try
            {
                string strSql = string.Format(@"SELECT 
                                                          a.*,
                                                          b.NAME,
                                                          c.unitname 
                                                        FROM
                                                          tbs001_developprojectbasicinfo a 
                                                          INNER JOIN
                                                          tbs001_constructionunit b 
                                                          ON a.js = b.ID 
                                                          INNER JOIN
                                                          tbs001_evaluationunit c 
                                                          ON a.hp = c.ID 
                                                         WHERE a.ReceiveState =1 ");
                if (StartDate != string.Empty && EndDate == string.Empty)
                    strSql += string.Format(" and a.xiangmushoulishijian >= '{0}'", StartDate);
                else if (StartDate == string.Empty && EndDate != string.Empty)
                    strSql += string.Format(" and a.xiangmushoulishijian <= '{0}'", EndDate);
                else if (StartDate != string.Empty && EndDate != string.Empty)
                    strSql += string.Format(" and a.xiangmushoulishijian between '{0}' and '{1}'", StartDate, EndDate);
                return DbHelperMySQL.Query(strSql).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 收款
        /// </summary>
        /// <param name="ProjectID"></param>
        /// <returns></returns>
        public static bool ReceiveMoney(string ProjectID)
        {
            try
            {
                string strSql = string.Format("UPDATE tbs001_developprojectbasicinfo SET ReceiveState=1 WHERE ID IN ({0})", ProjectID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 判断用户是否中心领导
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool WhetherLeader(string UserName)
        {
            try
            {
                string strSql = string.Format(@"SELECT ID FROM tbs001_user WHERE UserName='{0}' AND RoleId LIKE '%中心领导%'", UserName);
                return DbHelperMySQL.Query(strSql).Tables[0].Rows.Count > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 判断用户是否受理人
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool WhetherAcceptance(string UserName)
        {
            try
            {
                string strSql = string.Format(@"SELECT ID FROM tbs001_user WHERE UserName='{0}' AND RoleId LIKE '%受理人%'", UserName);
                return DbHelperMySQL.Query(strSql).Tables[0].Rows.Count > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 判断用户是否项目负责人
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool WhetherProHead(string UserName)
        {

            try
            {
                string strSql = string.Format(@"SELECT ID FROM tbs001_user WHERE UserName='{0}' AND RoleId LIKE '%项目负责人%'", UserName);
                return DbHelperMySQL.Query(strSql).Tables[0].Rows.Count > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 判断用户是否部门负责人
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool WhetherDepHead(string UserName)
        {
            try
            {
                string strSql = string.Format(@"SELECT ID FROM tbs001_user WHERE UserName='{0}' AND RoleId LIKE '%部门负责人%'", UserName);
                return DbHelperMySQL.Query(strSql).Tables[0].Rows.Count > 0;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 汇总上报材料保存
        /// </summary>
        /// <param name="Material"></param>
        /// <returns></returns>
        public static bool ReportSave(string Material)
        {
            try
            {
                string strSql = string.Format(@"update tbs001_developprojectbasicinfo set huizongshangbaoshumiancailiao='{0}',ReportingTrace =CONCAT(ReportingTrace,'|上报材料已选定({1}{2})') where ProjectName='{3}'"
                                                             , Material, HttpContext.Current.Session["UserName"].ToString(), DateTime.Now.Date.ToString("yyyy-MM-dd"), HttpContext.Current.Session["pjName"].ToString());
                return DbHelperMySQL.ExecuteSql(strSql) > 0;
            }
            catch
            {
                return false;
            }
        }


        #region MyRegion
        public static bool UploadURL(string Url, string ColumnName, string ID)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_projectleader SET " + ColumnName + "='" + Url + "' where ID=" + ID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0;
            }
            catch
            {
                return false;
            }
        }
        #endregion


        /// <summary>
        /// 上传评估报告
        /// </summary>
        /// <param name="Url"></param>
        /// <param name="ColumnName"></param>
        /// <returns></returns>
        public static bool UploadReport(string Url, string ColumnName)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_developprojectbasicinfo SET {2}='{0}' where ProjectName='{1}'"
                                                             , Url, HttpContext.Current.Session["pjName"].ToString(), ColumnName);
                return DbHelperMySQL.ExecuteSql(strSql) > 0;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 汇总上报材料确定
        /// </summary>
        /// <param name="Material"></param>
        /// <returns></returns>
        public static bool ReportOK(string Material)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_developprojectbasicinfo set ");
                TraceOper(ref br, "reportpeople", "reportdate", "ReportingTrace");
                br.AppendFormat(" huizongshangbaoshumiancailiao  = '{0}',", Material);
                br.AppendFormat(" reportstate=1, noticestate=0,DangQianLiuCheng='汇总上报',DangQianLiuChengBu='10'");
                br.AppendFormat(" where ProjectName ='{0}' ", HttpContext.Current.Session["pjName"].ToString());
                return ProOper(br.ToString());
            }
            catch
            {
                return false;
            }
        }


        /// <summary>
        /// 获取环评类别名称
        /// </summary>
        /// <returns></returns>
        public static string GetEIAType(int id)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" DataValue as Name ");
                br.AppendFormat(" from tbs001_basicdatasub where ID = '{0}' ", id);
                return DbHelperMySQL.Query(br.ToString()).Tables[0].Rows[0]["name"].ToString();
            }
            catch { return null; }
        }

        public static DevelopprojectbasicinfoDataCollection GetByProjectName(string where)
        {
            DevelopprojectbasicinfoDataCollection hs = new DevelopprojectbasicinfoDataCollection();
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" SELECT ");
                br.Append(" xiangmushouliren,xiangmukaopingchuliriqi,xiangmukaopingchuliren,reportstate,pingguyijianchuliren,pingguyijianchuliriqi,tachaquerenriqi,tachaquerenren,zhuanjiahuiyiquerenren,zhuanjiahuiyiquerenriqi,xiangmufuzequerenriqi,xiangmufuzequerenren,xiangmushoulishijian,fenpeiquerenshijian,fenpeiquerenren,reportdate,reportpeople,shoulituihuishanchu,fuzetuihuiqueren,tachaquerentuihui,ID");
                br.Append(" FROM tbs001_developprojectbasicinfo ");
                br.AppendFormat(" where ProjectName like '{0}' ", where);
                //DataTable dt = DbHelperMySQL.Query(br.ToString()).Tables[0];
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(br.ToString());
                while (dr.Read())
                {
                    DevelopprojectbasicinfoData model = new DevelopprojectbasicinfoData();
                    if (dr[0] != DBNull.Value)
                        model.xiangmushouliren = dr[0].ToString();
                    if (dr[1] != DBNull.Value)
                        model.xiangmukaopingchuliriqi = dr[1].ToString();
                    if (dr[2] != DBNull.Value)
                        model.xiangmukaopingchuliren = dr[2].ToString();
                    if (dr[3] != DBNull.Value)
                        model.reportstate = Convert.ToInt32(dr[3]);
                    if (dr[4] != DBNull.Value)
                        model.pingguyijianchuliren = dr[4].ToString();
                    if (dr[5] != DBNull.Value)
                        model.pingguyijianchuliriqi = dr[5].ToString();
                    if (dr[6] != DBNull.Value)
                        model.tachaquerenriqi = dr[6].ToString();
                    if (dr[7] != DBNull.Value)
                        model.tachaquerenren = dr[7].ToString();
                    if (dr[8] != DBNull.Value)
                        model.zhuanjiahuiyiquerenren = dr[8].ToString();
                    if (dr[9] != DBNull.Value)
                        model.zhuanjiahuiyiquerenriqi = dr[9].ToString();
                    if (dr[10] != DBNull.Value)
                        model.xiangmufuzequerenriqi = dr[10].ToString();
                    if (dr[11] != DBNull.Value)
                        model.xiangmufuzequerenren = dr[11].ToString();
                    if (dr[12] != DBNull.Value)
                        model.xiangmushoulishijian = dr[12].ToString();
                    if (dr[13] != DBNull.Value)
                        model.fenpeiquerenshijian = dr[13].ToString();
                    if (dr[14] != DBNull.Value)
                        model.fenpeiquerenren = dr[14].ToString();
                    if (dr[15] != DBNull.Value)
                        model.reportdate = dr[15].ToString();
                    if (dr[16] != DBNull.Value)
                        model.reportpeople = dr[16].ToString();
                    if (dr[17] != DBNull.Value)
                        model.shoulituihuishanchu = Convert.ToInt32(dr[17]);
                    if (dr[18] != DBNull.Value)
                        model.fuzetuihuiqueren = Convert.ToInt32(dr[18]);
                    if (dr[19] != DBNull.Value)
                        model.tachaquerentuihui = Convert.ToInt32(dr[19]);

                    if (dr[20] != DBNull.Value)
                        model.ID = Convert.ToInt32(dr[20]);
                    hs.Add(model);
                }
                return hs;
            }
            catch
            {
                return null;
            }
        }

        #region 保存/提交
        //保存
        public static bool Updatedbd(DevelopprojectbasicinfoData dbd)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_developprojectbasicinfo SET projectreportstate=0,ProjectAddress = '{0}',ProjectPerson = '{1}',Mobil = '{2}',EnvironmentalInvestmentProportion = '{3}',
                                                Investment = '{4}',GPSE = '{5}',Report = '{6}',jingdu = '{7}',weidu = '{8}',pingjiajingfei = '{9}',ESIndustryType = '{10}',huanpingleibie = '{11}',
                                                DevelopState = '{12}',BiangengTuihuichongbao = '{13}',IndustryType1 = '{14}',IndustryType2 = '{15}',IndustryType3 = '{16}',EIAIndustryType = '{17}',
                                                xmmsxx = '{18}',js = {19},hp = {20},xiangmufuzheren = '{21}',hplianxiren = '{22}',dianhua = '{23}',DangQianLiuCheng='待审核',DangQianLiuChengBu='00' WHERE ProjectName = '{24}' ",
                                                dbd.ProjectAddress, dbd.ProjectPerson, dbd.Mobil, dbd.EnvironmentalInvestmentProportion, dbd.Investment, dbd.GPSE, dbd.Report, dbd.jingdu, dbd.weidu,
                                                dbd.pingjiajingfei, dbd.ESIndustryType, dbd.huanpingleibie, dbd.DevelopState, dbd.BiangengTuihuichongbao, dbd.IndustryType1, dbd.IndustryType2,
                                                dbd.IndustryType3, dbd.EIAIndustryType, dbd.xmmsxx, dbd.js, dbd.hp, dbd.xiangmufuzheren, dbd.hplianxiren, dbd.dianhua, dbd.ProjectName);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            { return false; }
        }
        //提交
        public static bool UpdatedbdTiJiao(DevelopprojectbasicinfoData dbd)
        {
            try
            {
                string strSql = string.Format(@"UPDATE tbs001_developprojectbasicinfo SET projectreportstate=1,ProjectAddress = '{0}',ProjectPerson = '{1}',Mobil = '{2}',EnvironmentalInvestmentProportion = '{3}',
                                                Investment = '{4}',GPSE = '{5}',Report = '{6}',jingdu = '{7}',weidu = '{8}',pingjiajingfei = '{9}',ESIndustryType = '{10}',huanpingleibie = '{11}',
                                                DevelopState = '{12}',BiangengTuihuichongbao = '{13}',IndustryType1 = '{14}',IndustryType2 = '{15}',IndustryType3 = '{16}',EIAIndustryType = '{17}',
                                                xmmsxx = '{18}',js = {19},hp = {20},xiangmufuzheren = '{21}',hplianxiren = '{22}',dianhua = '{23}',DangQianLiuCheng='待审核',DangQianLiuChengBu='00',shoulituihuishanchu=NULL WHERE ProjectName = '{24}' ",
                                                dbd.ProjectAddress, dbd.ProjectPerson, dbd.Mobil, dbd.EnvironmentalInvestmentProportion, dbd.Investment, dbd.GPSE, dbd.Report, dbd.jingdu, dbd.weidu,
                                                dbd.pingjiajingfei, dbd.ESIndustryType, dbd.huanpingleibie, dbd.DevelopState, dbd.BiangengTuihuichongbao, dbd.IndustryType1, dbd.IndustryType2,
                                                dbd.IndustryType3, dbd.EIAIndustryType, dbd.xmmsxx, dbd.js, dbd.hp, dbd.xiangmufuzheren, dbd.hplianxiren, dbd.dianhua, dbd.ProjectName);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            { return false; }
        }
        #endregion
    }
}
